package edu.ucsd.cse135.gas.logic.support;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

public class University {

	private int id;
	private String name;
	private int countryId;
	private int country;
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getCountryId() {
		return countryId;
	}
	public void setCountryId(int countryId) {
		this.countryId = countryId;
	}
	public int getCountry() {
		return country;
	}
	public void setCountry(int country) {
		this.country = country;
	}
	
	/** Method to get all universities without custom ones **/
	
	public ArrayList<University> getUniversities(int locationId) {
		
		try {
			
			Connection conn = Database.open();
		
			String sql = "SELECT id, name FROM university WHERE location = ? AND custom = 0;";
		
			PreparedStatement preStmt = conn.prepareStatement(sql);
			preStmt.setInt(1, locationId);
			ResultSet rs = preStmt.executeQuery();
			
			ArrayList<University> uAl = new ArrayList<University>();
			
			while(rs.next())
			{
				/** Iterate over the whole result set and put the different universities into
				 *  a hash map and put this hash map afterwards into an array list for return**/
				
				University u = new University();
				
				u.setId(rs.getInt("id"));
				u.setName(rs.getString("name"));
				
				uAl.add(u);
			}
			
			rs.close();
			preStmt.close();
			Database.close();
			
			return uAl;
			
		}
		catch(Exception e) {
			
			Database.close();
			
			return null;
			
		}
	}
	
	/** Method to get all the locations where universities are **/
	
	public ArrayList<Location> getLocations() {
		
		try {
			
			Connection conn = Database.open();
			
			String sql = "SELECT location.id, location.name FROM location, university " +
						 "WHERE university.location = location.id " +
						 "GROUP BY location.id, location.name ORDER BY location.id;";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			ResultSet rs = preStmt.executeQuery();
			
			ArrayList<Location> lAl = new ArrayList<Location>(); 
			
			while(rs.next())
			{
				/** Iterate over the whole result set and put the different locations into
				 *  a hash map and put this hash map afterwards into an array list for return**/
				
				Location l = new Location();
				
				l.setId(rs.getInt("id"));
				l.setName(rs.getString("name"));
				
				lAl.add(l);
			}
			
			rs.close();
			preStmt.close();
			Database.close();
			
			return lAl;
			
		}
		catch(Exception e)
		{
			Database.close();
			return null;
		}
	}	
	
	/** Store custom university to database **/
	
	public static int storeCustomUniversity(String university, int location) {
		
		try {
			
			Connection conn = Database.open();
			
			boolean match = false;
			int id = -1;
			
			String sql = "SELECT id FROM university WHERE name = ? AND location = ?;";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			preStmt.setString(1, university);
			preStmt.setInt(2, location);
			ResultSet rs = preStmt.executeQuery();
			
			while(rs.next())
			{
				match = true;
				id = rs.getInt("id");
			}
			
			if(match == false)
			{
			
				Statement stmt = conn.createStatement();
				rs = stmt.executeQuery("SELECT nextval('university_id_seq') FROM university_id_seq;");
			
				rs.next();
				id = rs.getInt("nextval");
				
				sql = "INSERT INTO university (id, name, location) VALUES (?, ?, ?);";
				
				preStmt = conn.prepareStatement(sql);
				preStmt.setInt(1, id);
				preStmt.setString(2, university);
				preStmt.setInt(3, location);
				preStmt.executeUpdate();
				
				rs.close();
				preStmt.close();
				Database.close();
				
				return id;
			}
			
			rs.close();
			preStmt.close();
			Database.close();
			
			return id;
		}
		catch(Exception e)
		{
			Database.close();
			return -1;
		}
		
	}
}